﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;
using CNative.Utilities;

namespace CNative.DbUtils
{
    public abstract class BaseProvider
    {
        #region BaseProvider
        public BaseProvider(IDbHelper _db)
        {
            dbHelper = _db;
        }
        #endregion

        #region 关键字
        public readonly IDbHelper dbHelper = null;

        /// <summary>
        /// 数据库类型
        /// </summary>
        public virtual DatabaseType DBType { get { return DatabaseType.SqlServer; } }
        /// <summary>
        /// 参数关键字 @ or :
        /// </summary>
        public virtual string ParamKeyword
        {
            get
            {
                return "@";
            }
        }
        /// <summary>
        /// 关键字前缀 [
        /// </summary>
        public virtual string SuffixLeft
        {
            get
            {
                return "[";
            }
        }
        /// <summary>
        /// 关键字后缀 ]
        /// </summary>
        public virtual string SuffixRigh
        {
            get
            {
                return "]";
            }
        }
        #endregion

        #region DbProviderFactory
        /// <summary>
        /// 获取数据源类的提供者实例
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public virtual System.Data.Common.DbProviderFactory DbProviderFactory
        {
            get
            {
                return System.Data.SqlClient.SqlClientFactory.Instance;
            }
        }
        /// <summary>
        /// test.dbo.tablename
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbHelper"></param>
        /// <param name="tableType"></param>
        /// <returns></returns>
        public virtual string GetSchemaTableName(Type tableType)
        {
            var tb = Funs.GetDbTableInfo(dbHelper, tableType);
            if (tb == null)
            {
                return "";
            }
            return tb.Schema.IsNullOrEmpty() ? SuffixLeft + tb.TableName + SuffixRigh : SuffixLeft + tb.Schema + SuffixRigh + ".dbo." + SuffixLeft + tb.TableName + SuffixRigh;
        }
        #endregion

        #region CreateSqlEntity
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public virtual SqlEntity CreateSqlEntity(IDbHelper dbHelper)
        {
            return new SqlEntity(dbHelper);
        }
        #endregion

        #region GetDbParameter
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="parameters"></param>
        public void AddParameters(SqlEntity sqlent, params CNDbParameter[] dbParameters)
        {
            sqlent?.AddParameters(dbParameters);
        }
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="propName">参数名称</param>
        /// <param name="val">参数值</param>
        /// <param name="sqlent"></param>
        /// <param name="parameterName"></param>
        /// <param name="tb"></param>
        /// <param name="paramSuffix">参数前缀</param>
        public virtual bool AddDbParameter(IDbHelper dbHelper, string propName, object val, SqlEntity sqlent, out string parameterName, string paramSuffix = "wp_", DbTableInfo tb = null)
        {
            parameterName = "";
            if (sqlent == null) return false;
            paramSuffix = paramSuffix.NullToStr() + (sqlent.Parameters == null ? 0 : sqlent.Parameters.Count) + "_";

            var para = GetDbParameter(propName, val, paramSuffix, tb);
            if (para != null)
            {
                if (sqlent.Parameters == null)
                    sqlent.Parameters = new List<IDataParameter>();
                sqlent.Parameters.Add(para);

                parameterName = para.ParameterName;
                return true;
            }
            return false;
        }

        /// <summary>
        /// 获取DbParameter
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public virtual IDataParameter GetDbParameter(string propName, object val, string paramSuffix = "exp_",
            DbTableInfo tb = null, ParameterDirection direction = ParameterDirection.Input, DbType valDBType = DbType.String)
        {
            IDataParameter para = null;
            return para;
        }
        /// <summary>
        /// 入参处理
        /// </summary>
        /// <param name="propName"></param>
        /// <param name="val"></param>
        /// <param name="paramSuffix"></param>
        /// <returns></returns>
        protected virtual Tuple<bool, string, object> FillerParameter(string propName, object val, string paramSuffix = "exp_")
        {
            var parameterName = ParamKeyword + (paramSuffix + propName).TrimStart(ParamKeyword.ToCharArray());
            val = val ?? DBNull.Value;
            if (val?.Equals(DateTime.MinValue) == true) val = new DateTime(1970, 1, 1);
            val = ObjToDataTable(val);
            //--------------------------------------------------------------------------------------------------------------
            if (((val.IsNullOrEmpty() || (val is DateTime dat && dat <= DateTime.MinValue)) &&
                    (paramSuffix.StartsWith("valp_") || paramSuffix.StartsWith("insp_")) &&
                     (propName.ToLower() == "createdate" || propName.ToLower() == "updatedate" ||
                      propName.ToLower() == "createtime" || propName.ToLower() == "updatetime"))
                    || ((val.IsNullOrEmpty() || (val is DateTime dat2 && dat2 <= DateTime.MinValue)) &&
                        paramSuffix.StartsWith("setp_") &&
                        (propName.ToLower() == "updatedate" || propName.ToLower() == "updatetime")))
            {
                return Tuple.Create(true, parameterName, val);
            }
            return Tuple.Create(false, parameterName, val);
        }

        /// <summary>
        /// 入参值处理
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        protected virtual object ObjToDataTable(object obj)
        {
            if (obj.IsNullOrDBNull_() || obj is DataTable)
                return obj;
            if (obj is DateTime dat && dat <= DateTime.MinValue)
            {
                return DateTime.Now;
            }
            if (obj is Array || obj is System.Collections.IList)
            {
                var listobj = (obj as System.Collections.IEnumerable).Cast<object>().Select(x => x).ToList();
                if (listobj != null && listobj.Count > 0)
                {
                    var tb = new System.Data.DataTable();
                    tb.Columns.Add("Id", listobj[0].GetType());
                    listobj.ForEach(val =>
                    {
                        var row = tb.NewRow();
                        row[0] = val;
                        tb.Rows.Add(row);
                    });
                    return tb;
                }
            }
            return obj;
        }

        #region LookupDbType
        protected System.Data.DbType LookupDbType(Type type)
        {
            if (typeMap.TryGetValue(type, out DbType dbType))
            {
                return dbType;
            }
            //-----------------------------------------------------------
            if (type == Constants.ByteArrayType)
            {
                return System.Data.DbType.Binary;
            }
            else if (type == Constants.GuidType)
            {
                return System.Data.DbType.Guid;
            }
            else if (type == Constants.IntType)
            {
                return System.Data.DbType.Int32;
            }
            else if (type == Constants.ShortType)
            {
                return System.Data.DbType.Int16;
            }
            else if (type == Constants.LongType)
            {
                return System.Data.DbType.Int64;
            }
            else if (type == Constants.DateType)
            {
                return System.Data.DbType.DateTime;
            }
            else if (type == Constants.DobType)
            {
                return System.Data.DbType.Double;
            }
            else if (type == Constants.DecType)
            {
                return System.Data.DbType.Decimal;
            }
            else if (type == Constants.ByteType)
            {
                return System.Data.DbType.Byte;
            }
            else if (type == Constants.FloatType)
            {
                return System.Data.DbType.Single;
            }
            else if (type == Constants.BoolType)
            {
                return System.Data.DbType.Boolean;
            }
            else if (type == Constants.StringType)
            {
                return System.Data.DbType.String;
            }
            else if (type == Constants.DateTimeOffsetType)
            {
                return System.Data.DbType.DateTimeOffset;
            }
            //else if (type == Constants.TimeSpanType)
            //{
            //    if (this.Value != null)
            //        this.Value = this.Value.ToString();
            //}
            else if (type != null && type.IsEnum)
            {
                return System.Data.DbType.Int64;
            }
            return System.Data.DbType.String;

        }
        protected static Dictionary<Type, DbType>  typeMap = new Dictionary<Type, DbType>(37)
            {
                [typeof(byte)] = DbType.Byte,
                [typeof(sbyte)] = DbType.SByte,
                [typeof(short)] = DbType.Int16,
                [typeof(ushort)] = DbType.UInt16,
                [typeof(int)] = DbType.Int32,
                [typeof(uint)] = DbType.UInt32,
                [typeof(long)] = DbType.Int64,
                [typeof(ulong)] = DbType.UInt64,
                [typeof(float)] = DbType.Single,
                [typeof(double)] = DbType.Double,
                [typeof(decimal)] = DbType.Decimal,
                [typeof(bool)] = DbType.Boolean,
                [typeof(string)] = DbType.String,
                [typeof(char)] = DbType.StringFixedLength,
                [typeof(Guid)] = DbType.Guid,
                [typeof(DateTime)] = DbType.DateTime,
                [typeof(DateTimeOffset)] = DbType.DateTimeOffset,
                [typeof(TimeSpan)] = DbType.Time,
                [typeof(byte[])] = DbType.Binary,
                [typeof(byte?)] = DbType.Byte,
                [typeof(sbyte?)] = DbType.SByte,
                [typeof(short?)] = DbType.Int16,
                [typeof(ushort?)] = DbType.UInt16,
                [typeof(int?)] = DbType.Int32,
                [typeof(uint?)] = DbType.UInt32,
                [typeof(long?)] = DbType.Int64,
                [typeof(ulong?)] = DbType.UInt64,
                [typeof(float?)] = DbType.Single,
                [typeof(double?)] = DbType.Double,
                [typeof(decimal?)] = DbType.Decimal,
                [typeof(bool?)] = DbType.Boolean,
                [typeof(char?)] = DbType.StringFixedLength,
                [typeof(Guid?)] = DbType.Guid,
                [typeof(DateTime?)] = DbType.DateTime,
                [typeof(DateTimeOffset?)] = DbType.DateTimeOffset,
                [typeof(TimeSpan?)] = DbType.Time,
                [typeof(object)] = DbType.Object
            };
        #endregion
        #endregion

        #region MappingTypes
        /// <summary>
        /// 类型映射
        /// sqlTypeName，CsharpType，ParameterType
        /// </summary>
        public abstract List<Tuple<string, string, string>> MappingTypes { get; }
        public Tuple<string, string, string> AddMappingType(string sqlTypeName, string typeName, string parameterType)
        {
            var oldret = MappingTypes?.Find(f=>f.Item1== sqlTypeName);
            if (oldret != null) return oldret;
            var ret = Tuple.Create(sqlTypeName, typeName, parameterType);
            MappingTypes.Add(ret);
            return ret;
        }
        /// <summary>
        /// 获取库类型
        /// </summary>
        /// <param name="csharpTypeName"></param>
        /// <returns></returns>
        public virtual string GetDbTypeName(string csharpTypeName)
        {
            if (csharpTypeName == Constants.ByteArrayType.Name)
                return "varbinary";
            csharpTypeName = CheckCsharpTypeName(csharpTypeName);
            var mappings = this.MappingTypes?.Where(it => it.Item2.Equals(csharpTypeName, StringComparison.CurrentCultureIgnoreCase)).ToList();
            if (mappings != null && mappings.Count > 0)
                return mappings.First().Item1;
            else
                return "varchar";
        }
        protected virtual string CheckCsharpTypeName(string csharpTypeName)
        {
            if (csharpTypeName.ToLower() == "int32")
                csharpTypeName = "int";
            else if (csharpTypeName.ToLower() == "int16")
                csharpTypeName = "short";
            else if (csharpTypeName.ToLower() == "int64")
                csharpTypeName = "long";
            else if(csharpTypeName.ToLower().In("boolean", "bool"))
                csharpTypeName = "bool";

            return csharpTypeName;
        }
        /// <summary>
        /// 获取Csharp类型
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public virtual string GetCsharpTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item2 : "string";
        }
        /// <summary>
        /// 获取sql Parameter Type
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public virtual string GetParameterTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item3 : "SqlDbType.VarChar";
        }
        #endregion

        #region Sql Fun Templet
        #region 聚合函数
        /// <summary>
        /// SQL AVG() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的值的平均值</returns>
        public virtual string SQL_AVG(string column_name)
        {
            return $"AVG({column_name})";
        }
        /// <summary>
        /// SQL COUNT() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列值的数目</returns>
        public virtual string SQL_COUNT(string column_name)
        {
            return $"COUNT({(column_name.IsNullOrEmpty() ? "0" : column_name)})";
        }
        /// <summary>
        /// SQL MAX() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的最大值</returns>
        public virtual string SQL_MAX(string column_name)
        {
            return $"MAX({column_name})";
        }
        /// <summary>
        /// SQL MIN() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的最小值</returns>
        public virtual string SQL_MIN(string column_name)
        {
            return $"MIN({column_name})";
        }
        /// <summary>
        /// SQL SUM() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数返回指定列的总数</returns>
        public virtual string SQL_SUM(string column_name)
        {
            return $"SUM({column_name})";
        }
        #endregion

        /// <summary>
        /// SQL NVL() 从两个表达式返回一个非 null 值	函数
        /// </summary>
        /// <param name="check_expression">将被检查是否为 NULL的表达式。可以是任何类型的。</param>
        /// <param name="replacement_value">在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。</param>
        /// <returns>如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value</returns>
        public virtual string SQL_NVL(string check_expression, string replacement_value)
        {
            return $"ISNULL({check_expression},{replacement_value})";
        }

        /// <summary>
        /// SQL NOW() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>NOW() 函数返回当前系统的日期和时间</returns>
        public virtual string SQL_NOW()
        {
            return SqlDateNow;
        }

        /// <summary>
        /// SQL UCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为大写</returns>
        public virtual string SQL_UCASE(string column_name)
        {
            return $"UPPER({column_name})";
        }

        /// <summary>
        /// SQL LCASE() 函数
        /// </summary>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为小写</returns>
        public virtual string SQL_LCASE(string column_name)
        {
            return $"LOWER({column_name})";
        }

        /// <summary>
        /// SQL LEFT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public virtual string SQL_LEFT(string str, int length)
        {
            return $"LEFT({str},{length})";
        }
        /// <summary>
        /// SQL RIGHT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public virtual string SQL_RIGHT(string str, int length)
        {
            return $"RIGHT({str},{length})";
        }
        /// <summary>
        /// SQL SUBSTR(s, start, length) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>从字符串 str 的 start 位置截取长度为 length 的子字符串</returns>
        public virtual string SQL_SUBSTR(string str, int start, int length)
        {
            return $"SUBSTRING({str},{1 + start},{length})";
        }
        /// <summary>
        /// SQL TRIM(s) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>去掉字符串 str 开始和结尾处的空格</returns>
        public virtual string SQL_TRIM(string str)
        {
            return $"TRIM({str})";
        }
        /// <summary>
        /// SQL REPLACE(s,s1,s2) 函数
        /// </summary>
        /// <param name="s">列名</param>
        /// <returns>将字符串 s2 替代字符串 s 中的字符串 s1</returns>
        public virtual string SQL_REPLACE(string s, string s1, string s2)
        {
            return $"REPLACE({s},{s1},{s2})";
        }

        /// <summary>
        /// SQL CAST() 转换数据类型	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <param name="type">数据类型</param>
        /// <returns>转换数据类型</returns>
        public virtual string SQL_CAST(string column_name, string type)
        {
            return $"CAST({column_name} AS {type})";
        }

        public virtual string SQL_ToInt32(string column_name)
        {
            return SQL_CAST(column_name, "INT");
        }

        public virtual string SQL_ToInt64(string column_name)
        {
            return SQL_CAST(column_name, "BIGINT");
        }
        public virtual string SQL_ToDouble(string column_name)
        {
            return SQL_CAST(column_name, "FLOAT");
        }
        /// <summary>转换高精度的十进制数（一般用于货币）</summary>
        /// <param name="column_name"></param>
        /// <param name="d">小数位数</param>
        /// <returns></returns>
        public virtual string SQL_ToDecimal(string column_name, int d)
        {
            if (d < 0 || d > 10) d = 4;
            return SQL_CAST(column_name, "DECIMAL(18, " + d + ")");
        }

        public virtual string SQL_ToBool(string column_name)
        {
            return SQL_CAST(column_name, "BIT");
        }

        public virtual string SQL_ToVarchar(string column_name)
        {
            return SQL_CAST(column_name, "VARCHAR(MAX)");
        }

        public virtual string SQL_ToGuid(string column_name)
        {
            return SQL_CAST(column_name, "UNIQUEIDENTIFIER");
        }

        public virtual string SQL_ToDate(string column_name)
        {
            return SQL_CAST(column_name, "DATETIME");
        }

        public virtual string SQL_ToDateShort(string column_name)
        {
            return SQL_CAST(column_name, "DATE");
        }

        public virtual string SQL_ToTime(string column_name)
        {
            return SQL_CAST(column_name, "TIME");
        }
        #endregion

        #region SqlTemplet
        /// <summary>
        /// 获取数据库时间函数
        /// </summary>
        public virtual string SqlDateNow
        {
            get
            {
                return " GETDATE() ";
            }
        }
        /// <summary>
        /// 获取当前时间sql语句
        /// </summary>
        public virtual string FullSqlDateNow
        {
            get
            {
                return "SELECT GETDATE()";
            }
        }


        /// <summary>
        /// 通过连接字符串和表名获取数据库表的信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="DbName"></param>
        /// <returns></returns>
        public virtual SqlEntity GetDbTableInfoSQL(IDbHelper dbHelper, string tableName, string DbName)
        {
            var sqle = dbHelper.CreateSqlEntity();
            sqle.Sql = string.Format(GetColumnInfosByTableNameSql, DbName, tableName);
            return sqle;
        }
        /// <summary>
        /// 一般查询语句模板
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fieldsstr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public virtual string GetSelectSQL(String tbname, string fieldsstr, string orderbyStr, int top = 0)
        {
            string sqlMeta = @"SELECT " + (top > 0 ? " TOP " + top + " " : "") + fieldsstr + " FROM " + tbname + " WHERE {0} " + orderbyStr;
            return sqlMeta;
        }

        /// <summary>
        /// 分页查询语句模板
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="fieldsstr"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="page">第几页，从1开始</param>
        /// <param name="nums">每页记录数</param>
        /// <returns></returns>
        public virtual string GetSelectPageSQL(string tbName, string fieldsstr, string whereStr, string orderbyStr, int page = 1, int nums = 25)
        {
            if (page < 1) page = 1;
            if (nums < 1) nums = 10;
            var m = (page - 1) * nums + 1;
            var n = page * nums;

            var sqlMeta = "select * from("
                                 + "select " + fieldsstr + $" ,ROW_NUMBER() over({orderbyStr}) as RN  from {tbName} where {whereStr }) as t "
                              + $" where t.RN between {m} and {n}";
            return sqlMeta;
        }
        /// <summary>
        /// MERGE 合并语句模板
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="tbname"></param>
        /// <returns></returns>
        public virtual string GetMergeSql(String tbname)
        {
            var sqlMeta = @"MERGE " + tbname + " AS [target]  USING(SELECT 1 AS ____Id_____) AS source ON {0}  WHEN MATCHED THEN {1}  WHEN NOT MATCHED THEN {2};";

            return sqlMeta;
        }

        /// <summary>
        /// 更新语句模板
        /// "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ")
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public virtual string GetUpdateSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ");

            return sqlMeta;
        }
        /// <summary>
        /// 插入语句模板
        /// "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})"
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public virtual string GetInsertSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})";

            return sqlMeta;
        }
        /// <summary>
        /// 统计数量语句模板
        /// $"SELECT Count(0) FROM {fromStr} WHERE {0}";
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public virtual string GetCountSql(String fromStr, string fieldName = "")
        {
            var sqlMeta = "SELECT Count(" + (fieldName.IsNullOrEmpty() ? "0" : fieldName) + ") FROM " + fromStr + " WHERE {0}";

            return sqlMeta;
        }
        /// <summary>
        /// GetInsertSelectSql
        /// INSERT INTO " + insertTable + " ({0}) " + " SELECT {1} FROM " + selectTable + "  WHERE {2} 
        /// </summary>
        /// <param name="insertTable"></param>
        /// <param name="selectTable"></param>
        /// <param name="_dbType"></param>
        /// <returns></returns>
        public virtual string GetInsertSelectSql(String insertTable, String selectTable)
        {
            var sqlMeta = "INSERT INTO " + insertTable + " ({0}) " +
                            " SELECT {1} FROM " + selectTable + "  WHERE {2} ";

            return sqlMeta;
        }
        #endregion

        #region WithNextSequence
        /// <summary>
        /// 下个序列脚本
        /// </summary>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public virtual string WithNextSequence(string sequenceName = null)
        {
            if (string.IsNullOrEmpty(sequenceName))
            {
                sequenceName = "default_seq";
            }
            return $"nextval('{sequenceName}')";
        }
        #endregion

        #region DML CodeFirst
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        public virtual string GetDataBaseSql
        {
            get
            {
                return "SELECT Name FROM MASTER.DBO.SYSDATABASES ORDER BY Name";
            }
        }
        /// <summary>
        /// 获取数据库中的所有表
        /// </summary>
        public virtual string GetTableInfoListSql
        {
            get
            {
                return "SELECT name,xtype,(SELECT top 1 value FROM {0}.sys.extended_properties  WHERE {0}.sys.extended_properties.major_id = {0}.dbo.sysobjects.id  AND {0}.sys.extended_properties.minor_id=0) AS [Description] From {0}.dbo.sysobjects WHERE xtype = 'u'  ORDER BY name";
            }
        }
        /// <summary>
        /// 获取数据库中的所有视图
        /// </summary>
        public virtual string GetViewInfoListSql
        {
            get
            {
                return "SELECT name,xtype,(SELECT top 1 value FROM {0}.sys.extended_properties  WHERE {0}.sys.extended_properties.major_id = {0}.dbo.sysobjects.id  AND {0}.sys.extended_properties.minor_id=0) AS [Description] From {0}.dbo.sysobjects WHERE xtype = 'v'  ORDER BY name";
            }
        }

        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public virtual string GetColumnInfosByTableNameSql
        {
            get
            {
                return @"";
            }
        }
        #endregion

    }
}
